var mysql = require('mysql');
const moment = require('moment')
const table = 'tour'
const sqlConnection = function(queryCb) {
    var connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'app'
    });
    connection.connect();
    queryCb(connection)
    connection.end();
}

// const doQueryTotal = function(succ, fail) {
//     return sqlConnection(function(connection) {
//         var sqlTotal = `SELECT COUNT(*) as total FROM ${table}`;
//         connection.query(sqlTotal, function (err2, result2) {
//             if (err2) {
//                 fail(err2)
//                 return;
//             }
//             succ(result2[0].total)
//         });
//     })
// }
const doQuery = function (data, succ, fail) {
    return sqlConnection(function(connection) {
        const {page=1, pageSize=12} = data
        const offset = pageSize * (Number(page) -1);
        var sql = `SELECT * FROM ${table} LIMIT ${pageSize} OFFSET ${offset}`;
        // doQueryTotal(function(totalObj) {
            connection.query(sql, function (err, result) {
                if (err) {
                    fail(err)
                    return;
                }
                // succ({
                //     total: totalObj.total,
                //     lists: result
                // })
                succ(result)
            });
        // }, function(err) {
        //     fail(err)
        // })
    })
}


const doInsert = function (data, succ,fail) {
    return sqlConnection(function(connection) {
        var addSql = `INSERT INTO ${table}(id,title,description,tag,img,link,createTime,createBy) VALUES(null,?,?,?,?,?,?,?)`;
        var addSqlParams = [data.title, data.description, data.tag, data.img, data.link, moment().format('YYYY-MM-DD hh:mm:ss'),data.username];
        //增
        connection.query(addSql, addSqlParams, function (err, result) {
            if (err) {
                console.log('[INSERT ERROR] - ', err.message);
                fail(err)
                return;
            }
            succ(result)
            console.log('--------------------------INSERT----------------------------');
            //console.log('INSERT ID:',result.insertId);        
            console.log('INSERT ID:', result);
            console.log('-----------------------------------------------------------------\n\n');
        });
    
    })
}

const doUpdate = function (data, succ,fail) {
    return sqlConnection(function(connection) {
        var modSql = `UPDATE ${table} SET title = ?,description = ?,tag = ?,img = ?, link = ? , updateTime = ? ,updateBy = ? WHERE id = ?`;
        var modSqlParams = [data.title, data.description, data.tag, data.img, data.link, moment().format('YYYY-MM-DD hh:mm:ss'),data.username,];

        //改
        connection.query(modSql, modSqlParams, function (err, result) {
            if (err) {
                console.log('[UPDATE ERROR] - ', err.message);
                fail(err)
                return;
            }
            succ(result)
            console.log('--------------------------UPDATE----------------------------');
            console.log('UPDATE affectedRows', result.affectedRows);
            console.log('-----------------------------------------------------------------\n\n');
        });
    })
    
}


const doDelete = function (id, succ,fail) {
    return sqlConnection(function(connection) {
        var delSql = `DELETE FROM ${table} where id=${id}`;
        //删
        connection.query(delSql, function (err, result) {
            if (err) {
                console.log('[DELETE ERROR] - ', err.message);
                fail(err)
                return;
            }
            succ(result)
            console.log('--------------------------DELETE----------------------------');
            console.log('DELETE affectedRows', result.affectedRows);
            console.log('-----------------------------------------------------------------\n\n');
        });
    })
}

module.exports = {
    doQuery,
    doUpdate,
    doInsert,
    doDelete
}